package com.fishing.util;

import java.io.OutputStream;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExportFileUtil {
	private static Log logger = LogFactory.getLog(ExportFileUtil.class);
	
	/** 
     *  
     * @Title: processFileName 
     *  
     * @Description: ie,chrom,firfox下处理文件名显示乱码 
     */  
    public static String processFileName(HttpServletRequest request, String fileNames) {  
        String codedfilename = null;  
        try {  
            String agent = request.getHeader("USER-AGENT");  
            if (null != agent && -1 != agent.indexOf("MSIE") || null != agent  
                    && -1 != agent.indexOf("Trident")) {// ie  
   
                String name = java.net.URLEncoder.encode(fileNames, "UTF8");  
   
                codedfilename = name;  
            } else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,chrome等  
   
   
                codedfilename = new String(fileNames.getBytes("UTF-8"), "iso-8859-1");  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        return codedfilename;  
    }  
	
    
    public static void exportExcel(String [] headers,
    		Map<String,String> aliasHeaders,
			List<LinkedHashMap<String,Object>> datalist, 
			String fileName,
			HttpServletRequest request, 
			HttpServletResponse response) throws Exception{
    	
    	
    	HSSFWorkbook workbook = getWorkbook(headers,aliasHeaders,datalist);
		 // 生成提示信息，  
	    response.setContentType("application/vnd.ms-excel");  
	    OutputStream fOut = null;  
		// 进行转码，使其支持中文文件名  
		fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
		
		fileName = processFileName(request, fileName); 
       response.setHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
       fOut = response.getOutputStream();
		workbook.write(fOut);
		fOut.flush();
       fOut.close();
    }
    
	public static void exportExcel(String [] headers,
			List<LinkedHashMap<String,Object>> datalist, 
			String fileName,
			HttpServletRequest request, 
			HttpServletResponse response) throws Exception{
		
		exportExcel(headers, null, datalist, fileName, request, response);
		
	}
	
	public static HSSFWorkbook getWorkbook(Object [] keys,Map<String,String> aliasHeaders,List<LinkedHashMap<String,Object>> datalist){
		HSSFWorkbook workbook = null;
		// 这里的数据即时你要从后台取得的数据
		// 创建工作簿实例
		workbook = new HSSFWorkbook();
		// 创建工作表实例
		HSSFSheet sheet = workbook.createSheet("TscExcel");
		// 获取样式
		HSSFCellStyle style = createTitleStyle(workbook);
		
		if(datalist.size()>0){
			try {
				
				// 创建第一行标题,标题名字的本地信息通过resources从资源文件中获取
				HSSFRow row = sheet.createRow((short) 0);// 建立新行
				
				//设置表头列值
                for (int i = 0; i < keys.length; i++) {
                	String headValue = keys[i].toString();
                	if(null != aliasHeaders){
                		if(aliasHeaders.containsKey(headValue)){
                			headValue = aliasHeaders.get(headValue);
                		}
                	}
					createCell(row, i, style, Cell.CELL_TYPE_STRING, headValue);
				}
                for (int i = 0; i < datalist.size(); i++) {
                	HSSFRow newRow = sheet.createRow((short) (i+1));// 建立新行
                	//设置列值
                    for (int j = 0; j < keys.length; j++) {
    					createCell(newRow, j, style, Cell.CELL_TYPE_STRING, datalist.get(i).get(keys[j]));
    				}
				}

				//自适应调整列宽
				for (int i = 0; i < keys.length; i++) {
					sheet.autoSizeColumn((short)i);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}else{
			createCell(sheet.createRow(0), 0, style, Cell.CELL_TYPE_STRING, "无数据");
		}
		
		return workbook;
	}
	
	
	// 设置excel的title样式
	public static HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
		return createTitleStyle(wb, null);
	}
	
	// 设置excel的title样式
	public static HSSFCellStyle createTitleStyle(HSSFWorkbook wb, Short bg) {
		HSSFFont boldFont = wb.createFont();
		boldFont.setFontHeight((short) 200);
		HSSFCellStyle style = wb.createCellStyle();
		style.setFont(boldFont);
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
		
		style.setBorderTop((short) 1);  
        style.setBorderRight((short) 1);  
        style.setBorderBottom((short) 1);  
        style.setBorderLeft((short) 1);  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        if(null != bg){
        	style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        	style.setFillForegroundColor(bg);
        }
		return style;
	}
	
	public static HSSFCellStyle createTitleStyleBold(HSSFWorkbook wb){
		return createTitleStyleBold(wb, null);
	}
	
	// 设置excel的title样式
		public static HSSFCellStyle createTitleStyleBold(HSSFWorkbook wb, Short bg) {
//			HSSFFont boldFont = wb.createFont();
//			boldFont.setFontHeight((short) 200);
//			HSSFCellStyle style = wb.createCellStyle();
//			style.setFont(boldFont);
//			style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
			
	        HSSFCellStyle style = wb.createCellStyle(); // 样式对象
			
			HSSFFont headFont = wb.createFont();  
	        headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
	        headFont.setFontName("宋体");  
	        headFont.setFontHeightInPoints((short) 11);  
	        
	        style.setFont(headFont);  
	        style.setBorderTop((short) 1);  
	        style.setBorderRight((short) 1);  
	        style.setBorderBottom((short) 1);  
	        style.setBorderLeft((short) 1);  
	        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
	        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	        
	        if(null != bg){
	        	style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	        	style.setFillForegroundColor(bg);
	        }
			return style;
		}

	// 创建Excel单元格
	public static void createCell(HSSFRow row, int column, HSSFCellStyle style, int cellType, Object value) {
		HSSFCell cell = row.createCell(column);
		if (style != null) {
			cell.setCellStyle(style);
		}
		switch (cellType) {
		case Cell.CELL_TYPE_BLANK: {
		}
			break;
		case Cell.CELL_TYPE_STRING: {
			cell.setCellValue(value==null?"":value.toString());
		}
			break;
		case Cell.CELL_TYPE_NUMERIC: {
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			cell.setCellValue(Double.parseDouble(value.toString()));
		}
			break;
		default:
			break;
		}
	}
	
	
	/**
	 * 合并单元格
	 * 
	 * @param sheet
	 *            要合并单元格的excel 的sheet
	 * @param cellLine
	 *            要合并的列
	 * @param startRow
	 *            要合并列的开始行
	 * @param endRow
	 *            要合并列的结束行
	 */
	public static void addMergedRegion(HSSFSheet sheet, int cellLine,
			int startRow, int endRow, HSSFWorkbook workBook) {

		HSSFCellStyle style = workBook.createCellStyle(); // 样式对象

		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
		style.setAlignment(CellStyle.ALIGN_LEFT);// 水平
		
		sheet.addMergedRegion(new CellRangeAddress(
				startRow, endRow, cellLine, cellLine));
		HSSFRow row = sheet.getRow(startRow);
		String cellValueTemp = sheet.getRow(startRow)
				.getCell(cellLine).getStringCellValue();
		HSSFCell cell = row.createCell(cellLine);
		cell.setCellValue(cellValueTemp); // 跨单元格显示的数据
		cell.setCellStyle(style); // 样式
		
	}
	
	
	/**
	 * 合并单元格
	 * 
	 * @param sheet
	 *            要合并单元格的excel 的sheet
	 * @param cellLine
	 *            要合并的列
	 * @param startRow
	 *            要合并列的开始行
	 * @param endRow
	 *            要合并列的结束行
	 */
	public static void addMergedRegionRow(HSSFSheet sheet, int cellRow,
			int startCell, int endCell, HSSFWorkbook workBook) {

		HSSFCellStyle style = workBook.createCellStyle(); // 样式对象

		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
		style.setAlignment(CellStyle.ALIGN_LEFT);// 水平
		
		sheet.addMergedRegion(new CellRangeAddress(
				cellRow, cellRow, startCell, endCell));
		HSSFRow row = sheet.getRow(cellRow);
		String cellValueTemp = sheet.getRow(cellRow)
				.getCell(startCell).getStringCellValue();
		HSSFCell cell = row.createCell(cellRow);
		cell.setCellValue(cellValueTemp); // 跨单元格显示的数据
		cell.setCellStyle(style); // 样式
		
	}
	
	/**
	 * 合并单元格
	 * 
	 * @param sheet
	 *            要合并单元格的excel 的sheet
	 * @param cellLine
	 *            要合并的列
	 * @param startRow
	 *            要合并列的开始行
	 * @param endRow
	 *            要合并列的结束行
	 */
//	public static void addMergedRegion(HSSFSheet sheet, int cellLine,
//			int startRow, int endRow, HSSFWorkbook workBook) {
//
//		HSSFCellStyle style = workBook.createCellStyle(); // 样式对象
//
//		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
//		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
//		// 获取第一行的数据,以便后面进行比较
//		String s_will = sheet.getRow(startRow).getCell(cellLine)
//				.getStringCellValue();
//		System.out.println("s_will:"+s_will);
//		int count = 0;
//		boolean flag = false;
//		for (int i = 1; i <= endRow; i++) {
//			String s_current = sheet.getRow(i).getCell(cellLine).getStringCellValue();
//			System.out.println("s_current:"+s_current);
//			if (s_will.equals(s_current)) {
//				s_will = s_current;
//				if (flag) {
//					sheet.addMergedRegion(new CellRangeAddress(
//							startRow - count, startRow, cellLine, cellLine));
//					HSSFRow row = sheet.getRow(startRow - count);
//					String cellValueTemp = sheet.getRow(startRow - count)
//							.getCell(0).getStringCellValue();
//					HSSFCell cell = row.createCell(0);
//					cell.setCellValue(cellValueTemp); // 跨单元格显示的数据
//					cell.setCellStyle(style); // 样式
//					count = 0;
//					flag = false;
//				}
//				startRow = i;
//				count++;
//			} else {
//				flag = true;
//				s_will = s_current;
//			}
//			// 由于上面循环中合并的单元放在有下一次相同单元格的时候做的，所以最后如果几行有相同单元格则要运行下面的合并单元格。
//			if (i == endRow && count > 0) {
//				sheet.addMergedRegion(new CellRangeAddress(endRow - count,
//						endRow, cellLine, cellLine));
//				String cellValueTemp = sheet.getRow(startRow - count)
//						.getCell(0).getStringCellValue();
//				HSSFRow row = sheet.getRow(startRow - count);
//				HSSFCell cell = row.createCell(0);
//				cell.setCellValue(cellValueTemp); // 跨单元格显示的数据
//				cell.setCellStyle(style); // 样式
//			}
//		}
//	}
	
	
	
	
	public static void createSheetRows(HSSFWorkbook workbook,HSSFSheet sheet,Object [] keys,List<LinkedHashMap<String,Object>> datalist){
		// 获取样式
		HSSFCellStyle style = createTitleStyle(workbook);
		
		if(datalist.size()>0){
			try {
				
				// 创建第一行标题,标题名字的本地信息通过resources从资源文件中获取
				HSSFRow row = sheet.createRow((short) 0);// 建立新行
				
				//设置表头列值
                for (int i = 0; i < keys.length; i++) {
                	String headValue = keys[i].toString();
					createCell(row, i, style, Cell.CELL_TYPE_STRING, headValue);
				}
                for (int i = 0; i < datalist.size(); i++) {
                	HSSFRow newRow = sheet.createRow((short) (i+1));// 建立新行
                	//设置列值
                    for (int j = 0; j < keys.length; j++) {
    					createCell(newRow, j, style, Cell.CELL_TYPE_STRING, datalist.get(i).get(keys[j]));
    				}
				}

				//自适应调整列宽
				for (int i = 0; i < keys.length; i++) {
					sheet.autoSizeColumn((short)i);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}else{
			createCell(sheet.createRow(0), 0, style, Cell.CELL_TYPE_STRING, "无数据");
		}
	}

}
